﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Threading;
using System.Diagnostics;


namespace ShoozlaWCFService
{
/// <summary>
    /// singletone
    /// </summary>
    public class DBHelperInstance
    {
        // Provide the following information
        private static string _userName = "bertelli";
        private static string _password = "**********"; //--> password removed for privacy (mine!)
        private static string _dataSource = "tcp:sql2005-3.reinventinc.com";
        private static string _databaseName = "gianlucabertelli";
        private static SqlConnection _conn = null;
        private static SqlCommand _command = null;
        private static DBHelperInstance _instance = null;

        private DBHelperInstance()
        {
            Initialize();
        }

        private void Initialize()
        {
            if(_conn == null)
                _conn = new SqlConnection(ConnectionString().ToString());
            if (_command == null) 
                _command = _conn.CreateCommand();
        }
        
        public static DBHelperInstance GetInstance()
        {
            if (_instance == null)
                _instance = new DBHelperInstance();

            return _instance;
        }

        private SqlConnectionStringBuilder ConnectionString()
        {

            // Create a connection string for the database
            SqlConnectionStringBuilder connString2Builder;
            connString2Builder = new SqlConnectionStringBuilder();
            connString2Builder.DataSource = _dataSource;
            connString2Builder.InitialCatalog = _databaseName;
            connString2Builder.Encrypt = false;
            connString2Builder.TrustServerCertificate = false;
            connString2Builder.UserID = _userName;
            connString2Builder.Password = _password;
            return connString2Builder;
        }

        /// <summary>
        /// Execute the Query to the DB
        /// </summary>
        /// <param name="Query">select query</param>
        /// <returns>result set, null in case of errors</returns>
        public SqlDataReader QueryToDB(String Query)
        {
            SqlDataReader rs = null;
            try
            {

                using (var mutex = new Mutex())
                {
                    if(mutex.WaitOne())
                    {
                        if (_conn.State != ConnectionState.Open)
                            _conn.Open();

                        _command.CommandText = Query;
                        rs = _command.ExecuteReader();
                        mutex.ReleaseMutex();
                    }
                }
                
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
                rs= null; //an error occurred         
            }

            return rs;

        }

        /// <summary>
        /// Execute the Query to the DB
        /// </summary>
        /// <param name="query">one of INSERT,DELETE,UPDATE</param>
        /// <returns>number of rows affected, negative in case of error</returns>
        public int NonQueryToDB(String query)
        {
            
            if (String.IsNullOrEmpty(query))
                return 0;

            int res = -1;
            try
            {
                using (var mutex = new Mutex())
                {
                    if (mutex.WaitOne())
                    {
                        if (_conn.State != ConnectionState.Open)
                            _conn.Open();

                        _command.CommandText = query;
                        res = _command.ExecuteNonQuery();
                        mutex.ReleaseMutex();
                    }
                }
            }
            catch (Exception e)
            {
                res = -1; //an error occurred  
                Debug.WriteLine("SQLException:" + e.Message);
            }
            return res;
        }

       
    }
}
